﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
namespace DAL
{
    public class FoodService
    {
        public static List<Food> SelectAllByClassID(int classID)
        {
            string sql;
            if (classID != 0)
            {
                sql = "select * from Food where Typeid=" + classID;
            }
            else
            {
                sql = "select * from Food";
            }

            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);

            List<Food> list = new List<Food>();
            while (dr.Read())
            {
                Food FM = new Food();

                FM.Foodid = (int)dr["Foodid"];
                FM.Fname = dr["Fname"].ToString();
                FM.Typeid = (int)dr["Typeid"];
                FM.Price = Convert.ToDecimal(dr["Price"]);
                FM.Url = dr["Url"].ToString();
                FM.Describe = dr["Describe"].ToString();

                list.Add(FM);
            }
            dr.Close();
            return list;
        }
        public static List<Food> SelectByID(int id)
        {
            string sql = "select * from Food where Foodid=" + id;
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);

            List<Food> list = new List<Food>();
            Food FM = new Food();
            while (dr.Read())
            {
                FM.Foodid = (int)dr["Foodid"];
                FM.Fname = dr["Fname"].ToString();
                FM.Typeid = (int)dr["Typeid"];
                FM.Price = Convert.ToDecimal(dr["Price"]);
                FM.Url = dr["Url"].ToString();
                FM.Describe = dr["Describe"].ToString();

                list.Add(FM);
            }
            dr.Close();
            return list;
        }
        public static  List<Food> SelectFood(string s)
        {
            string sql = string.Format("select * from Food where Fname like '%{0}%'",s);
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            List<Food> list = new List<Food>();
            
            while (dr.Read())
            {
                Food FM = new Food();
                FM.Foodid = (int)dr["Foodid"];
                FM.Fname = dr["Fname"].ToString();
                FM.Typeid = (int)dr["Typeid"];
                FM.Price = Convert.ToDecimal(dr["Price"]);
                FM.Url = dr["Url"].ToString();
                FM.Describe = dr["Describe"].ToString();
                list.Add(FM);
            }
            dr.Close();
            return list;
        }
        public static List<Food> SelectFoodByPage(int count,int pageIndex)
        {
            string sql = string.Format("select top {0} * from Food a join FoodType b " +
                 "on a.Typeid=b.Typeid where Foodid not in " +
                 "(select top {1} Foodid from Food)", count, 10 * (pageIndex - 1));
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            List<Food> list = new List<Food>();
            while (dr.Read())
            {
                Food f = new Food();

                f.Foodid = dr.GetInt32(0);
                f.Fname = dr.GetString(1);
                f.Typeid = dr.GetInt32(2);
                f.Price = dr.GetDecimal(3);
                f.Url = dr.GetString(4);
                f.Describe = dr.GetString(5);

                f.TypeName = dr.GetString(7);
                list.Add(f);
            }
            dr.Close();
            return list;
        }
        public static List<Food> SelectAll()
        {
            string sql = "select * from Food a join FoodType b on a.Typeid=b.Typeid";
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            List<Food> list = new List<Food>();
            while (dr.Read())
            {
                Food f = new Food();

                f.Foodid = dr.GetInt32(0);
                f.Fname = dr.GetString(1);
                f.Typeid = dr.GetInt32(2);
                f.Price = dr.GetDecimal(3);
                f.Url = dr.GetString(4);
                f.Describe = dr.GetString(5);
               
                f.TypeName= dr.GetString(7);
                list.Add(f);
            }
            dr.Close();
            return list;
        }
        public static Food SelectPrice(int id)
        {
            string sql = "select Price from Food where Foodid="+id;
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            Food f = new Food();
            while (dr.Read())
            {
               
                f.Price = dr.GetDecimal(0);


            }
            dr.Close();
            return f;
        }
        public static bool Insert(Food f)
        {
            string sql = string.Format("insert Food values ('{0}',1,{1},'{2}','{3}')", f.Fname, f.Price, f.Url, f.Describe);
            return DBHelperSQL.ExecuteNonQuery(sql);
        }
        public static List<Food> SelectByPage(int count, int pageIndex)
        {
            string sql1 = string.Format("select top {0}  * from Food a join FoodType b  on a.Typeid=b.Typeid where Foodid not in(select top {1}  Foodid from Food) ", count, 20 * (pageIndex - 1));
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql1);
            List<Food> list = new List<Food>();
            while (dr.Read())
            {
                Food f = new Food();

                f.Foodid = dr.GetInt32(0);
                f.Fname = dr.GetString(1);

                f.Price = dr.GetDecimal(3);
                f.Url = dr.GetString(4);
                f.Describe = dr.GetString(5);
                f.TypeName = dr.GetString(7);
                list.Add(f);
            }
            dr.Close();
            return list;
        }
        public static List<FoodType> SelectAllty()//全查
        {
            string sql = "select * from FoodType";
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);

            List<FoodType> list = new List<FoodType>();
            while (dr.Read())
            {
                FoodType p = new FoodType();

                p.Typeid = (int)dr["typeid"];
                p.TypeName = dr["TypeName"].ToString();

                list.Add(p);
            }
            dr.Close();
            return list;
        }
        public static List<Food> SelectAllByClassIDd(int Typeid)
        {
            string sql;
            if (Typeid != 0)
            {
                sql = "select * from Food a join FoodType b on a.Typeid=b.Typeid  where a.Typeid=" + Typeid;
            }
            else
            {
                sql = "select * from Food a join FoodType b on a.Typeid=b.Typeid ";
            }

            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);

            List<Food> list = new List<Food>();
            while (dr.Read())
            {
                Food f = new Food();

                f.Foodid = dr.GetInt32(0);
                f.Fname = dr.GetString(1);
                f.Typeid = dr.GetInt32(2);
                f.Price = dr.GetDecimal(3);
                f.Url = dr.GetString(4);
                f.Describe = dr.GetString(5);
                f.TypeName = dr.GetString(7);

                list.Add(f);
            }
            dr.Close();
            return list;
        }
        public static bool Delete(int Foodid)
        {
            string sql = "delete from Food where Foodid =" + Foodid;
            return DBHelperSQL.ExecuteNonQuery(sql);
        }
        public static Food SelectByp(int Foodid)
        {
            Food f = new Food();
            string sql = "select * from Food  a join FoodType b on a.Typeid=b.Typeid where a.Foodid=" + Foodid;

            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            List<Food> list = new List<Food>();
            while (dr.Read())
            {


                f.Foodid = dr.GetInt32(0);
                f.Fname = dr.GetString(1);
                f.Typeid = dr.GetInt32(2);
                f.Price = dr.GetDecimal(3);
                f.Url = dr.GetString(4);
                f.Describe = dr.GetString(5);
                f.TypeName = dr.GetString(7);
                list.Add(f);
            }
            dr.Close();
            return f;
        }
        public static bool Update(Food f)
        {
            string sql = string.Format("update Food set Price ='{0}' where Foodid='{1}'",
               f.Price, f.Foodid);
            return DBHelperSQL.ExecuteNonQuery(sql);

        }

    }
}
